﻿using Newtonsoft.Json.Linq;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Web;

namespace XBJCYGC.Controllers
{
    public class SHCXBBController : ApiController
    {

        //table yhdwdm  yhjb
        [CYGCExceptionFilter("获取该表单位下拉时出错")]
        public DataTable DWLIST(JObject opt)
        {
            var table = opt["table"].ToString();
            var yhdwdm = opt["yhdwdm"].ToString();
            var yhjb = opt["yhjb"].ToString();
            if (HttpRuntime.Cache[table + yhdwdm] != null)
                return (DataTable)HttpRuntime.Cache[table + yhdwdm];
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("DWMC", typeof(string)));
            dt.Columns.Add(new DataColumn("DWDM", typeof(string)));
            DataTable tmp, tmp2;
            string dwdms = "";
            string s_bbhtjjb = "";
            OracleParameter[] para;
            para = new OracleParameter[1]{
                        new OracleParameter("tname",OracleDbType.Varchar2,table.ToLower(),ParameterDirection.Input)};
            //1、先获取该表的统计级别
            s_bbhtjjb = DB.CYGC.QueryOneValue("SELECT flag1 FROM sys_fl WHERE fllb = '数据表' AND flmc =:tname ", para).ToString();

            if (yhjb == "9") //显示 厂+油田  或厂+联污
            {
                #region 分公司
                if (s_bbhtjjb.IndexOf("2", 0) >= 0)
                {
                    #region  油田区块的情况
                    //请求中原+采油厂
                    tmp = DB.CYGC.Query("select '&nbsp;&nbsp;'||dwjc  AS DWMC,dwdm from sys_org where  (tjjb='9' or tjjb='3')  and yxbz='1' order by px");
                    dwdms = "";
                    if (tmp.Rows.Count > 0)
                    {
                        foreach (DataRow r in tmp.Rows)
                        {
                            dwdms += ("'" + r["DWDM"] + "',");
                        }
                        //插入中原+采油厂
                        dt.Rows.Add("全部采油厂", dwdms.TrimEnd(new char[] { ',' }));
                        //请求三级单位的菜单
                        tmp2 = DB.CYGC.Query("select dwdm from sys_org where (tjjb='9' or tjjb='3' or tjjb='2')  and yxbz='1' order by px");
                        dwdms = "";
                        if (tmp2.Rows.Count > 0)
                        {
                            foreach (DataRow r in tmp2.Rows)
                            {
                                dwdms += ("'" + r["DWDM"] + "',");
                            }
                            dt.Rows.Add("全部单位", dwdms.TrimEnd(new char[] { ',' }));
                        }
                        //插入中原与采油厂的每一个分项。    
                        dt.Merge(tmp);
                    }
                    //请求中原+油田
                    tmp = DB.CYGC.Query("select '&nbsp;&nbsp;'||dwjc AS DWMC,dwdm from sys_org where (dwdm='1' or tjjb='2')  and yxbz='1' order by px");
                    dwdms = "";
                    if (tmp.Rows.Count > 0)
                    {
                        foreach (DataRow r in tmp.Rows)
                        {
                            dwdms += ("'" + r["DWDM"] + "',");
                        }
                    }
                    dt.Rows.Add("全部油田", dwdms.TrimEnd(new char[] { ',' }));
                    tmp.Rows.RemoveAt(0); //移除第一行的中原
                    dt.Merge(tmp);
                    #endregion
                }
                else if (s_bbhtjjb.IndexOf("4", 0) >= 0)
                {
                    #region 联
                    //请求中原+联合站
                    tmp = DB.CYGC.Query("select '&nbsp;&nbsp;'||dwjc AS DWMC,dwdm from sys_org where (tjjb in('4') or dwdm='1')  and yxbz='1'  order by px");
                    dwdms = "";
                    if (tmp.Rows.Count > 0)
                    {
                        foreach (DataRow r in tmp.Rows)
                        {
                            dwdms += ("'" + r["DWDM"] + "',");
                        }
                        dt.Rows.Add("污水站", dwdms.TrimEnd(new char[] { ',' }));
                        tmp.Rows.RemoveAt(0);
                        dt.Merge(tmp);
                    }

                    //请求中原+厂
                    tmp = DB.CYGC.Query("select '&nbsp;&nbsp;'||dwjc AS DWMC,dwdm from sys_org where tjjb in('9' ,'3')  and yxbz='1'  order by px");
                    dwdms = "";
                    if (tmp.Rows.Count > 0)
                    {
                        foreach (DataRow r in tmp.Rows)
                        {
                            dwdms += ("'" + r["DWDM"] + "',");
                        }
                        dt.Rows.Add("全部采油厂", dwdms.TrimEnd(new char[] { ',' }));
                        //请求三级单位的菜单
                        tmp2 = DB.CYGC.Query("select dwdm from sys_org where (tjjb='9' or tjjb='3' or tjjb='4')  and yxbz='1'  order by px");
                        dwdms = "";
                        if (tmp2.Rows.Count > 0)
                        {
                            foreach (DataRow r in tmp2.Rows)
                            {
                                dwdms += ("'" + r["DWDM"] + "',");
                            }
                            dt.Rows.Add("全部单位", dwdms.TrimEnd(new char[] { ',' }));
                        }
                        dt.Merge(tmp);
                    }
                    #endregion
                }
                else //只显示中原+采油厂
                {
                    #region 厂级表
                    tmp = DB.CYGC.Query("select '&nbsp;&nbsp;'||dwjc  AS DWMC,dwdm from sys_org where (tjjb='9' or tjjb='3') and yxbz='1'  order by px");
                    dwdms = "";
                    if (tmp.Rows.Count > 0)
                    {
                        foreach (DataRow r in tmp.Rows)
                        {
                            dwdms += ("'" + r["DWDM"] + "',");
                        }
                        dt.Rows.Add("全部单位", dwdms.TrimEnd(new char[] { ',' }));
                        dt.Merge(tmp);
                    }
                    #endregion
                }
                #endregion
            }
            else if (yhjb == "3")  //显示厂+区  或厂+联污
            {
                #region 采油厂
                para = new OracleParameter[1]{
                        new OracleParameter("yhdwdm",OracleDbType.Varchar2,yhdwdm,ParameterDirection.Input)
                    };
                if (s_bbhtjjb.IndexOf("2", 0) >= 0)
                {
                    #region 油田区块情况
                    //请求厂+油田
                    tmp = DB.CYGC.Query("select '&nbsp;&nbsp;'||dwjc AS DWMC,dwdm from sys_org where tjjb in('2','3') and cdm=:yhdwdm  order by px", para);
                    dwdms = "";
                    if (tmp.Rows.Count > 0)
                    {
                        foreach (DataRow r in tmp.Rows)
                        {
                            dwdms += ("'" + r["DWDM"] + "',");
                        }
                        dt.Rows.Add("全部油田", dwdms.TrimEnd(new char[] { ',' }));
                        tmp.Rows.RemoveAt(0);
                        dt.Merge(tmp);
                    }
                    #endregion
                }
                else if (s_bbhtjjb.IndexOf("4", 0) >= 0)
                {
                    #region 联
                    //请求厂+联
                    tmp = DB.CYGC.Query("select '&nbsp;&nbsp;'||dwjc AS DWMC,dwdm from sys_org where tjjb in('4' ,'3') and cdm=:yhdwdm order by px", para);
                    dwdms = "";
                    if (tmp.Rows.Count > 0)
                    {
                        foreach (DataRow r in tmp.Rows)
                        {
                            dwdms += ("'" + r["DWDM"] + "',");
                        }
                        dt.Rows.Add("全部单位", dwdms.TrimEnd(new char[] { ',' }));
                        dt.Merge(tmp);
                    }
                    #endregion
                }
                else
                {
                    #region 厂级表
                    dt.Merge(DB.CYGC.Query("select '&nbsp;&nbsp;'||dwjc  AS DWMC,dwdm from sys_org where dwdm=:yhdwdm", para));
                    #endregion
                }
                #endregion
            }
            HttpRuntime.Cache[table + yhdwdm] = dt;
            return dt;
        }

        //返回三个数据：锁定状态，签名情况，请求的报表数据
        //qmdwdm是指，查询的单位如果是多个，则显示查询用户单位的签名情况。如果查询单位是1个，则显示该单位的签名情况。
        [CYGCExceptionFilter("查询报表数据时出错")]
        public object QUERY(JObject opt)
        {
            var view = opt["view"].ToString();
            var nyfw = opt["nyfw"].ToString();
            var cxdwdm = opt["cxdwdm"].ToString();
            var shbz = opt["shbz"].ToString();
            var ny = opt["ny"].ToString();
            int locked = 0;  //暂时不锁
            DataTable dtData;
            OracleParameter[] para = null;
            /* if (opt.yhjb == "9")
             {
                 locked = 0; //分公司用户无锁
             }
             else 
             if (opt.yhjb == "3")  //厂用户上报或锁定之后，均是锁定
             {
                 para = new OracleParameter[1]{
                     new OracleParameter("ny",OracleDbType.Varchar2,opt.ny,ParameterDirection.Input)
                  };
                 var lockstate = DB.CYGC.QueryOneValue("select lockstate from SYS_LOCKSTATE t where ny=:ny and dwdm='1'", para);//查询分公司1的锁定状态
                 var sbstate = DB.CYGC.QueryOneValue("select sbbz from sys_shxx t where ny=:ny and dwdm='" + opt.cdm + "'", para);//查询分上报标志
                 if ((lockstate != null && lockstate.ToString() == "1") || (sbstate != null && sbstate.ToString() == "1"))
                 {
                     locked = 1;
                 }
             }*/
            //所有用户都用一样查报表数据。只有一个单位时，需要补上两边的单引号
            if (!cxdwdm.Contains("'"))
            {
                cxdwdm = "'" + cxdwdm + "'";
            }
            string sql = string.Format("select * from {0} where ny in({1}) and dwdm in({2}) and shbz in({3}) order by dwpx,flpx,ny,shbz ", view, nyfw, cxdwdm, shbz);
            dtData = DB.CYGC.Query(sql);
            //如果没有查到数据，则只插入审核数据，再查询一次
            if (dtData.Rows.Count == 0)
            {
                para = new OracleParameter[1]{
                    new OracleParameter("ny",OracleDbType.Varchar2,ny,ParameterDirection.Input)
                 };
                DB.CYGC.Procedure("pck_cygc.createbb", para);
                dtData = DB.CYGC.Query(
                        string.Format("select * from {0} where ny in({1}) and dwdm in({2}) and shbz in({3}) order by dwpx,flpx,ny ", view, nyfw, cxdwdm, shbz)
                     );
            }

            return new
            {
                locked = locked,
                data = dtData
            };
        }

        //当前报表计算,直接再进行校验
        //  
        [CYGCExceptionFilter("重新计算错误")]
        public DataTable BBJS(JObject opt)
        {
            var ny = opt["ny"].ToString();
            var yhdwdm = opt["yhdwdm"].ToString();
            var table = opt["table"].ToString();
            var bbh = opt["bbh"].ToString();
            var calcflag = opt["calcflag"].ToString();
            var shbz = "1";
            OracleParameter[] para;
            //计算全部数据，则把所有有数据标脏
            if (calcflag == "1")
            {
                para = new OracleParameter[4]{
                                new OracleParameter("ny",OracleDbType.Varchar2,ny,ParameterDirection.Input),
                                new OracleParameter("dwdm",OracleDbType.Varchar2,yhdwdm,ParameterDirection.Input),
                                new OracleParameter("dwdm",OracleDbType.Varchar2,yhdwdm,ParameterDirection.Input),
                                new OracleParameter("dwdm",OracleDbType.Varchar2,yhdwdm,ParameterDirection.Input)
                            };
                DB.CYGC.Execute($"update {table} set isdirty='1' where ny=:ny and (dwdm=:dwdm or dwdm in(select dwdm from sys_org where cdm=:dwdm) or '1'=:dwdm)", para);
            }
            //重新审核计算
            para = new OracleParameter[4]{
                                new OracleParameter("ny",OracleDbType.Varchar2,ny,ParameterDirection.Input),
                                new OracleParameter("dwdm",OracleDbType.Varchar2,yhdwdm,ParameterDirection.Input),
                                new OracleParameter("tables",OracleDbType.Varchar2,table,ParameterDirection.Input),
                                new OracleParameter("shbz",OracleDbType.Varchar2,shbz,ParameterDirection.Input)
                            };
            DB.CYGC.Procedure("pck_cygc.recalcbb", para);
            //校验
            para = new OracleParameter[4]{
                                new OracleParameter("ny",OracleDbType.Varchar2,ny,ParameterDirection.Input),
                                new OracleParameter("dwdm",OracleDbType.Varchar2,yhdwdm,ParameterDirection.Input),
                                new OracleParameter("shbz",OracleDbType.Varchar2,shbz,ParameterDirection.Input),
                                new OracleParameter("bbh",OracleDbType.Varchar2,bbh,ParameterDirection.Input)
                            };
            DB.CYGC.Procedure("pck_cygc.validatebb", para);

            //取校验结果
            DataTable dt = new DataTable();
            para = new OracleParameter[5]{
                                 new OracleParameter("ny",OracleDbType.Varchar2,ny,ParameterDirection.Input),
                                 new OracleParameter("bbh",OracleDbType.Varchar2,bbh,ParameterDirection.Input),
                                 new OracleParameter("yhdwdm",OracleDbType.Varchar2,yhdwdm,ParameterDirection.Input),
                                 new OracleParameter("yhdwdm",OracleDbType.Varchar2,yhdwdm,ParameterDirection.Input),
                                 new OracleParameter("yhdwdm",OracleDbType.Varchar2,yhdwdm,ParameterDirection.Input)
                            };
            dt = DB.CYGC.Query(@"
        select a.ny,a.dwmc,a.fl,a.jyms,a.jyzz,a.jyyz,a.bbh from LOG_VALIDATE a,sys_org b 
        where a.ny=:ny and a.shbz='1' and a.bbh=:bbh and (a.dwdm=:yhdwdm or a.dwdm in(select dwdm from sys_org where cdm=:yhdwdm) or '1'=:yhdwdm )  
        and a.dwdm=b.dwdm
        and a.tjjb>=2
        order by b.px,a.bbh", para);
            return dt;
        }

        [CYGCExceptionFilter("指标穿透错误")]
        [HttpPost]
        public DataTable SQL_CT(JObject opt)
        {
            //过程直接返回结果集
            OracleParameter[] para = new OracleParameter[6]{
                        new OracleParameter("ny",OracleDbType.Varchar2,opt["ny"],ParameterDirection.Input),
                        new OracleParameter("dwdm",OracleDbType.Varchar2,opt["dwdm"],ParameterDirection.Input),
                        new OracleParameter("bmc",OracleDbType.Varchar2,opt["bmc"],ParameterDirection.Input),
                        new OracleParameter("fl",OracleDbType.Varchar2,opt["fl"],ParameterDirection.Input),
                        new OracleParameter("zdm",OracleDbType.Varchar2,opt["zdm"],ParameterDirection.Input),
                        new OracleParameter("ret",OracleDbType.RefCursor,null,ParameterDirection.Output)
                    };
            DataTable dt = DB.CYGC.ProcedureReturnDt("pck_cygc.tjxx_hz_ct", para);
            return dt;
        }
    }
}
